<?php
if(!defined('IN')) exit('Access Denied');

//优化： 引入memcache

class model_sql{
	var $db;
	var $qdb;
	var $DB_MySQL;

	function __construct(){
		global $DB_MySQL;
		$this->DB_MySQL = $DB_MySQL;
		$this->select_db('dq');
	}
	function select_db($s){
		$this->db = new sql_db($this->DB_MySQL[$s]['hostname'], $this->DB_MySQL[$s]['username'], $this->DB_MySQL[$s]['password'], $this->DB_MySQL[$s]['database']);
	}
	function getAvatar($app_openid,$app_openkey){
		$cpc_qplus = new cpc_qplus();
		Return $cpc_qplus->getAvatar($app_openid,$app_openkey);
	}
	//用户信息
	function getUserInfo($user_id){
		global $Global;
		//$this->select_db('q');	//更换数据库连接地,更换后，记得更换回来。 $this->select_db('dq');
		$sql = "select * from `info_link_user` where `id`='$user_id'";
		$res = $this->db->fetch_one($sql);
		if(!empty($res)){
			if(!empty($res['photo'])){
				$res['avatar'] = $Global['picurl'].'avatar/'.getImgName($res['photo'],'1');
				$res['avatar2'] = $Global['picurl'].'avatar/'.getImgName($res['photo'],'2');
				$res['avatar3'] = $Global['picurl'].'avatar/'.getImgName($res['photo'],'3');
			}else{
				$res['avatar'] = $res['avatar2'] = $res['avatar3'] = $res['face'];
				//$res['avatar'] = $res['avatar2'] = $res['avatar3'] = $this->getAvatar($res['app_openid'],$res['app_openkey']);
			}
			if($res['gender'] == '1'){
				$res['sex'] = '男';
			}elseif($res['gender'] == '2'){
				$res['sex'] = '女';
			}else{
				$res['sex'] = '';
			}
			$year = date("Y");
			if(!empty($res['birthyear']) and $year>$res['birthyear']){
				//$month = date("m");
				//$day = date("d");
				$res['age'] = $year - $res['birthyear'];
			}
		}
		Return $res;
	}

	//获取某用户独自动态,多种动态的集合
	function getUserShow($user_id){
		//获得勋章
		//优惠卷
	}

	//获取某用户周边动态,多种动态的集合,个人中心里面使用
	function getUserShowAll($user_id){
		//获得勋章
		//优惠卷
		//朋友关注
	}

	/**
	 ** 最近来访
	 ** 返回：array(user_id,nick,avatar)
	**/
	function getUserVisit($user_id,$page=0,$num=6){
		$start = $page*$num;
		//考虑只读取 photo, 如果没提交的，从接口读取
		$sql = "select v.`visitor_id`,u.`id` as user_id,u.`nick`,u.`city`,u.`photo`,u.app_openid,u.app_openkey,u.`face` from `info_q_visit` as v left join `info_link_user` as u on v.`visitor_id`=u.`id` where v.`user_id`='$user_id' group by v.`visitor_id` order by v.dateline desc limit $start,$num";
		$res = $this->db->fetch_all($sql);
		$re = $this->getUserAvatar($res);
		Return $re;
	}
	/**
	 ** 推荐宠友
	 ** 返回：array(user_id,nick,avatar)
	**/
	function getUserCommendFriend($user_id,$num=6){
		//推荐规则, 有同类宠物，宠友的宠友，宠物明星.......
		//有同类宠物
		$sql = "select p.user_id,u.`nick`,u.`city`,u.`photo`,u.app_openid,u.app_openkey,u.`face` from `info_user_pet` as p left join `info_link_user` as u on p.`user_id`=u.`id` where (p.user_id not in(select `user_id` from `info_q_follow` where `follow_id`='$user_id') or p.user_id not in(select `follow_id` from `info_q_follow` where `user_id`='$user_id')) and p.goods_id in(select goods_id from `info_user_pet` where `user_id`='$user_id') and p.user_id<>'$user_id' group by p.user_id order by rand()  limit $num";
		$res = $this->db->fetch_all($sql);
		$re = $this->getUserAvatar($res);
		//宠友的宠友
		//宠物明星
		Return $re;
	}
	/**
	 ** 宠物列表
	 ** 返回：array(pid,name,pic)
	**/
	function getPetList($user_id){
		global $Global;
		$sql = "select `id`,`name`,`pic` from `info_user_pet` where `user_id`='$user_id' order by dateline desc limit 6";
		$res = $this->db->fetch_all($sql);
		foreach($res as $key=>&$val){
			$val['pic'] = $Global['picurl'].'pic/'.$val['pic'];
		}
		Return $res;
	}
	/**
	 ** 某个宠物信息
	 ** 返回：
	**/
	function getPets($pid){
		global $Global;
		$sql = "select * from `info_user_pet` where `id`='$pid'";
		$res = $this->db->fetch_one($sql);
		if(!empty($res)){
			if($res['sex'] == '1'){
				$res['sex'] = '男';
			}elseif($res['sex'] == '0'){
				$res['sex'] = '女';
			}else{
				$res['sex'] = '';
			}
			$res['sort'] = $Global['pet']['kind'][$res['sort']];
			$res['kind'] = $Global['pet']['kind'][$res['kind']];
			$res['pic'] = $Global['picurl'].'pic/'.$res['pic'];
		}
		Return $res;
	}
	/**
	 ** 喜欢某宠物的人
	 ** 返回：array(user_id,nick,avatar,city)
	**/
	function getLikePetUser($pid){
		$sql = "select p.`user_id`,u.photo,u.nick,u.city,u.app_openid,u.app_openkey,u.face from `info_pet_qlike` as p left join `info_link_user` as u on p.`user_id`=u.`id` where p.`pet_id`='$pid'";
		$res = $this->db->fetch_all($sql);
		$re = $this->getUserAvatar($res);
		Return $re;
	}
	/**
	 ** 好友列表
	 ** 参数：$user_id => 登录用户;  $k 1全部,2互相关注,3我关注的,4关注我的
	 ** 返回：array(user_id,avatar,nick,province,city,state,pet=>array(id,name,sort_name,sort_id)); state=>1我关注,2关注我,3相互关注
	**/
	function getFriendIdList($user_id,$k='',$page=0,$num=8){
		$start = $page*$num;
		$re = $ids1 = $ids2 = array();
		//我关注的
		$sql = "select `user_id`,`dateline` from `info_q_follow` where `follow_id`='$user_id' order by `dateline` desc";
		$re1 = $this->db->fetch_all($sql);
		foreach($re1 as $key=>&$val){
			$val['follow_state'] = '3';
			$ids1[] = $val['user_id'];
		}
		//关注我的
		$sql = "select `follow_id` as user_id,`dateline` from `info_q_follow` where `user_id`='$user_id' order by `dateline` desc";
		$re2 = $this->db->fetch_all($sql);
		foreach($re2 as $key=>&$val){
			if(in_array($val['user_id'],$ids1)){
				$val['follow_state'] = '2';
			}else{
				$val['follow_state'] = '4';
			}
			$ids2[] = $val['user_id'];
		}

		//变更标记，关注，互相关注
		foreach($re1 as $key=>&$val){
			if(in_array($val['user_id'],$ids2)){
				$val['follow_state'] = '2';
			}
		}

		if($k == '1'){		//全部
			$re3 = array_merge($re1,$re2);	//合并
			if(!empty($re3)){
				$re4 = array();
				foreach($re3 as $key=>$val){
					$volume[$key] = $val['dateline'];
					$edition[$key] = $val['user_id'];
				}
				array_multisort($volume, SORT_DESC, $edition, SORT_ASC, $re3);	//排序
				foreach($re3 as $key=>$val){
					$re[$key]['user_id'] = $val['user_id'];
					$re4[$key]['follow_state'] = $val['follow_state'];
				}
				$re = array_unique($re);	//去除重复
				$re = array_slice($re,$start,$num);	//数组截取
				foreach($re as $key=>&$val){
					$val['follow_state'] = $re4[$key]['follow_state'];
				}
			}
		}elseif($k == '2'){	//互相关注
			$re3 = array_intersect($re1,$re2);	//取交集
			if(!empty($re3)){
				$re4 = array();
				foreach($re3 as $key=>$val){
					$volume[$key] = $val['dateline'];
					$edition[$key] = $val['user_id'];
				}
				array_multisort($volume, SORT_DESC, $edition, SORT_ASC, $re3);	//排序
				foreach($re3 as $key=>$val){
					$re[$key]['user_id'] = $val['user_id'];
					$re4[$key]['follow_state'] = $val['follow_state'];
				}
				$re = array_unique($re);	//去除重复
				$re = array_slice($re,$start,$num);	//数组截取
				foreach($re as $key=>&$val){
					$val['follow_state'] = $re4[$key]['follow_state'];
				}
			}
		}elseif($k == '3'){	//我关注的
			$re1 = array_slice($re1,$start,$num);	//数组截取
			$re = $re1;
		}elseif($k == '4'){	//关注我的
			$re2 = array_slice($re2,$start,$num);	//数组截取
			$re = $re2;
		}
		Return $re;
	}

	/**
	 ** 优惠卷列表
	 ** 返回：array(id,serial,price,state,dateline,info)
	**/
	function getCouponList($user_id,$k=''){
		if(empty($k)){		//全部
			
		}
	}
	/**
	 ** 获取某人道具列表
	 ** 返回：array(id,pic_url,name,info,num)
	**/
	function getSceneList($user_id){
		
	}
	/**
	 ** 获取某人道具使用列表	m 某类功能记录, k 全部类别的记录
	 ** 返回：array(id,name,dateline,num,price)
	**/
	function getSceneUseList($user_id,$m='',$k=''){
		if(empty($m)){	//全部分类道具功能记录
			
		}
		if(empty($k)){	//全部分类道具记录
			
		}
	}
	/**
	 ** 获取某人勋章列表
	 ** 返回：array(id,pic_url,name)
	**/
	function getMedalList($user_id){
		
	}
	/**
	 ** 记录来访客户
	 ** 参数：$user_id => 被访问者id; $visitor_id => 访问者id
	 ** 返回：true/false
	**/
	function addVisitUser($user_id='',$visitor_id=''){
		if($user_id==$visitor_id or empty($user_id) or empty($visitor_id)) Return false;
		$dateline = time();
		$this->db->query("insert into `info_q_visit`(`user_id`,`visitor_id`,`dateline`) values('$user_id','$visitor_id','$dateline')");
		Return true;
	}
	/**
	 ** 消息-系统通知
	 ** 返回：array(id,title,content,dateline)
	**/
	function getNewsNotice($user_id,$page=0,$num=10){
		$start = $page*$num;
		$sql = "select f.`id`,n.`title`,n.`content`,f.`state`,f.`dateline` from `info_q_noticeids` as f left join `info_q_notice` as n on f.`notice_id`=n.`id` where f.`user_id`='$user_id' order by f.`dateline` desc  limit $start,$num";
		$re = $this->db->fetch_all($sql);
		Return $re;
	}
	/**
	 ** 消息-私信
	 ** 返回：array()
	**/
	function getNewsPM($user_id,$page=0,$num=10){
		$start = $page*$num;
		$sql = "select count(m.`id`) as num,m.`id`,m.`small_user_id`,m.`big_user_id`,m.`send_user_id`,m.`content`,m.`dateline`,u.id as user_id,u.photo,u.nick,u.city,u.app_openid,u.app_openkey,u.face from (select * from `info_q_message` where (`small_user_id`='".$user_id."' and `active`<>'1') or (`big_user_id`='".$user_id."' and `active`<>'2') order by `dateline` desc) as m  left join `info_link_user` as u on m.`send_user_id`=u.`id` group by m.`small_user_id`,m.`big_user_id` order by m.`dateline` desc limit $start,$num";
		$res = $this->db->fetch_all($sql);
		$re = $this->getUserAvatar($res);
		Return $re;
	}
	/**
	 ** 消息-私信回复列表
	 ** 返回：array()
	**/
	function getNewsPMAnswer($user_id,$other_user_id,$page=0,$num=10){
		$start = $page*$num;
		$sql = "select m.`id`,m.`small_user_id`,m.`big_user_id`,m.`send_user_id`,m.`content`,m.`dateline`,u.id as user_id,u.photo,u.nick,u.city,u.app_openid,u.app_openkey,u.face from `info_q_message` as m left join `info_link_user` as u on m.`send_user_id`=u.`id` where (m.`small_user_id`='".$user_id."' and m.`big_user_id`='".$other_user_id."' and m.`active`<>'1') or (m.`small_user_id`='".$other_user_id."' and m.`big_user_id`='".$user_id."' and m.`active`<>'2') order by m.`dateline` desc limit $start,$num";
		$res = $this->db->fetch_all($sql);
		$re = $this->getUserAvatar($res);
		Return $re;
	}
	/**
	 ** 用户头像选择与转换
	 ** 返回：array()
	**/
	function getUserAvatar($res = array()){
		global $Global;
		$re = array();
		if(!empty($res)){
			foreach($res as $key=>$val){
				if(empty($val['photo'])){
					$val['avatar'] = $val['avatar2'] = $val['avatar3'] = $val['face'];
					//$val['avatar'] = $val['avatar2'] = $val['avatar3'] = $this->getAvatar($val['app_openid'],$val['app_openkey']);
				}else{
					$val['avatar'] = $Global['picurl'].'avatar/'.getImgName($val['photo'],'1');
					$val['avatar2'] = $Global['picurl'].'avatar/'.getImgName($val['photo'],'2');
					$val['avatar3'] = $Global['picurl'].'avatar/'.getImgName($val['photo'],'3');
				}
				$re[$key] = $val;
			}
		}
		Return $re;
	}
	/*===================================================  Friend start  =========================================================*/
	/**
	 ** 交友首页用户与宠物列表
	 ** 返回：array(user_info, pet=>array(pet_info))
	**/
	function getUserPetList($user_id_list){
		$re = array();
		if(is_array($user_id_list)){
			foreach($user_id_list as $key=>$val){
				$u = $this->getUserInfo($val['user_id']);
				$p = $this->getPetList($val['user_id']);
				$pp = array();
				if(!empty($p)){
					foreach($p as $key2=>$val2){
						$pp[] = $this->getPets($val2['id']);
					}
				}
				$pu = array('pet'=>$pp);
				if(empty($u)){
					$u = array();
				}
				if(!empty($val['follow_state'])){
					$follow_state = array('follow_state'=>$val['follow_state']);
				}else{
					$follow_state = array();
				}
				$re[] = array_merge($pu,$u,$follow_state);
			}
		}
		Return $re;
	}
	/**
	 ** 交友 最新登陆
	 ** 返回：array(user_id)
	**/
	function searchNewLoginUser($term=array(),$page=0,$num=5){
		$start = $page*$num;
		//相同城市
		//养同品种
		//男
		//女
		//想养
		if(empty($term)){	//全部
			$sql = "select id as user_id from `info_link_user` order by `lastlogin` desc limit $start,$num";
			$re = $this->db->fetch_all($sql);
		}
		Return $re;
	}
	/**
	 ** 交友 最高人气
	 ** 返回：array(user_id)
	**/
	function searchTopLikeUser($term=array(),$page=0,$num=5){
		
	}
	/**
	 ** 交友 最新喜欢
	 ** 返回：array(user_id)
	**/
	function searchNewLikeUser($term=array(),$page=0,$num=5){
		
	}
	/**
	 ** 交友 型男靓女
	 ** 返回：array(user_id)
	**/
	function searchBoyGirlUser($term=array(),$page=0,$num=5){
		
	}
	/**
	 ** 交友 明星会员
	 ** 返回：array(user_id)
	**/
	function searchStarMemberUser($term=array(),$page=0,$num=5){
		
	}
	/**
	 ** 交友 勋章达人
	 ** 返回：array(user_id)
	**/
	function searchMedalMasterUser($term=array(),$page=0,$num=5){
		
	}
	/*===================================================  Friend end  =========================================================*/
}